package Server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * Used to query MySQL database.
 * @author PZK
 *
 */
public class Database {
	
	Connection connection = null;
	Statement statement = null;
	ResultSet resultSet = null;
	
	public Database(String url, String username, String password) throws SQLException, ClassNotFoundException{
		
		Class.forName("com.mysql.jdbc.Driver");
		
		connection = DriverManager.getConnection(url, username, password);
		statement = connection.createStatement();
		resultSet = statement.executeQuery("SELECT version()");
		
		while(resultSet.next()){
			System.out.println(resultSet.getString(1));
		}
		
	}
	
	public boolean login(String username, String password) throws SQLException{
		
		Boolean result = false;

		resultSet = statement.executeQuery("SELECT * FROM user WHERE Username = '"+username+"' and Password = '"+password+"'");
		if(resultSet.next()){
			result = true;
		}
		
		return result;
		
	}
	
	public ArrayList<Project> getAllProjects() throws SQLException{
		
		ArrayList<Project> result = new ArrayList<Project>();
		
		resultSet = statement.executeQuery("SELECT * from Projects");
		
		while(resultSet.next()){
			result.add(new Project(resultSet.getInt(1), resultSet.getString(2)));
		}
		
		for(int j = 0; j < result.size(); j++){
			
			resultSet = statement.executeQuery("SELECT A.Username FROM user AS A," +
					" depends AS B, projects AS C WHERE C.ProjectID = '"+result.get(j).getId()+"' AND " +
					"B.project = '"+result.get(j).getId()+"' AND B.user = A.User_ID;");
			while(resultSet.next()){
				result.get(j).addUser(resultSet.getString(1));
			}
			
		}
		
		return result;
		
	}
	
	public ArrayList<String> getAllUsers() throws SQLException{
		
		ArrayList<String> result = new ArrayList<String>();
		
		resultSet = statement.executeQuery("SELECT * from User");
		
		while(resultSet.next()){
			
			result.add(resultSet.getString(2));
			
		}
		
		return result;
		
	}
	public void closeResults() throws SQLException{
		
		if(resultSet != null)
			resultSet.close();
		if(statement != null)
			statement.close();
		if(connection != null)
			connection.close();
		
	}

}
